<?php
/**
 * Akimimi front
 * CommentDao
 * by M.Wang
 */
require_once '../app/core/models/BaseDao.php';
class CommentDao extends BaseDao{
    private $tableName = 'comments';
    
    public function __construct( $dbUtil ){
        parent::__construct($dbUtil);
        $this->_name = $this->tableName;
    }
    
    public function getProductAvgPoint( $int_productid ){
        $str_sql = "
            SELECT `average_comment_point` FROM `products`
                WHERE `id` = ? 
        ";
        $arr_results = $this->dbUtil->SelectData($str_sql, array($int_productid));
        if( !empty( $arr_results[0] ) )
            return $arr_results[0]['average_comment_point'];
        return false;
    }
    
    public function countProductAvgPoint( $int_productid ){
        $str_sql = "
            SELECT AVG(`point`) AS `avg_point` FROM `{$this->tableName}`
                WHERE `product_id` = ?
                    AND `is_delete` = 0 AND `point` > 0
                GROUP BY `product_id`
        ";
        $arr_results = $this->dbUtil->SelectData($str_sql,array($int_productid));
        if( !empty( $arr_results[0] ) )
            return $arr_results[0]['avg_point'];
        return false;
    }
    
    public function insertComment( $arr_map ){
    	if(phpversion()>='5.1.0'){
    	   date_default_timezone_set('Asia/Shanghai');
    	}
    	if(!isset($arr_map['created']))
    	   $arr_map['created'] = date('Y-m-d H:i:s');
    	if(!isset($arr_map['modified']))
    	   $arr_map['modified'] = date('Y-m-d H:i:s');
        if( !$this->insert( $arr_map ) )
            return false;
        $float_avgPoint = $this->countProductAvgPoint( $arr_map['product_id'] );
        if( $float_avgPoint === false )
            return false;
        require_once 'ProductDao.php';
        $productDao = new ProductDao( $this->dbUtil );
        return $productDao->update(array('average_comment_point'=>$float_avgPoint), " `id`='{$arr_map['product_id']}'");
    }
    
    public function deleteComments( $int_id ){
        return $this->update(array('is_delete'=>1), 'id=' . $int_id);
    }
    
    public function getCommentsNumberByProductId($int_productid = 0){
        ////////////////////////////////////////////////////////////////////
    	if($int_productid == 0){
        	$str_sql = "
            SELECT count(*) as opinionNumber
            FROM `{$this->tableName}` AS `c`
                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
            WHERE  `c`.`is_delete` = 0 ";
            return $this->dbUtil->SelectData( $str_sql, array(  ) );
        }else{
        	$str_sql = "
            SELECT count(*) as opinionNumber
            FROM `{$this->tableName}` AS `c`
                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
            WHERE `c`.`product_id` = ? AND `c`.`is_delete` = 0 ";
            return $this->dbUtil->SelectData( $str_sql, array( $int_productid ) );
        }
    	
    }
    
    /**
     * 求当前产品的评论人数
     */
    public function getCommentsUserNumberByProductId($int_productid = 0){
        if(empty($int_productid)){
        	$str_sql = "
            select  distinct(c.user_id)
            from comments c
            where `c`.`is_delete` = 0 ";
        	return $this->dbUtil->SelectData( $str_sql, array() );
        }else{
	    	$str_sql = "
	            select  distinct(c.user_id)
				from comments c
				where c.product_id = ? and `c`.`is_delete` = 0 ";
	        return $this->dbUtil->SelectData( $str_sql, array( $int_productid ) );
        }
    }
    
    public function getCommentsByProductId($int_productid, $int_page=null, $int_limit=null){
        if( is_null( $int_page ) ){
            $int_page = 0;
            $int_limit = 20;
        }
        $str_sql = "
            SELECT 
                `c`.`content`,`c`.`point`,`c`.`created`,
                `r`.`content` as `reply_content`,
                `r`.`created` as `reply_created`,
                `u`.`name` as `username`,
                `u`.`rank` as `userrank`
            FROM `{$this->tableName}` AS `c`
                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
            WHERE `c`.`product_id` = ? AND `c`.`is_delete` = 0 
                
                ORDER BY `c`.`created` DESC 
                LIMIT {$int_page}, {$int_limit}
        ";
                //GROUP BY `r`.`comment_id`
                
        return $this->dbUtil->SelectData( $str_sql, array( $int_productid ) );
    }
    
    /*
     * 显示产品所有评论
     */
    public function getAllCommentsByPid($int_productid = 0){
        if($int_productid == 0){
        	$str_sql = "
            SELECT 
                count(*) as num
            FROM `{$this->tableName}` AS `c`
            WHERE  `c`.`is_delete` = 0                
	        ";
	        return $this->dbUtil->SelectData( $str_sql, array() );
        }else{
	        $str_sql = "
	            SELECT 
	                count(*) as num
	            FROM `{$this->tableName}` AS `c`
	            WHERE `c`.`product_id` = ? AND `c`.`is_delete` = 0                  
	        ";
	                //GROUP BY `r`.`comment_id`
	        return $this->dbUtil->SelectData( $str_sql, array( $int_productid ) );
        }
    }
    
    /**
     * 产品所有评论页面，显示评论页数总数
     */
    public function getAllCommentsNumberByPid($int_productid = 0){
    	if($int_productid == 0){
	        $str_sql = "SELECT count(*) as num
	            FROM `{$this->tableName}` AS `c`
	                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
	                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
	            WHERE  `c`.`is_delete` = 0 and `r`.`content` IS NOT NULL";
	        return $this->dbUtil->SelectData( $str_sql, array(  ) );
    	}else{
	        $str_sql = "SELECT count(*) as num
	            FROM `{$this->tableName}` AS `c`
	                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
	                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
	            WHERE `c`.`product_id` = ?  `c`.`is_delete` = 0 and `r`.`content` IS NOT NULL";
	        return $this->dbUtil->SelectData( $str_sql, array( $int_productid ) );
    	}
        
    }
    
    /*
     * 显示产品所有评论
     */
    public function getAllCommentsByProductId($int_productid = 0, $int_page=null, $int_limit=null){
        if( is_null( $int_page ) ){
            $int_page = 0;
            $int_limit = 20;
        }
        if($int_productid == 0){
        	$str_sql = "
            SELECT 
                `c`.`content`,`c`.`point`,`c`.`created`,`c`.`product_id`,`c`.`user_id`,
                `r`.`content` as `reply_content`,
                `r`.`created` as `reply_created`,
                `u`.`name` as `username`,
                `u`.`rank` as `userrank`
            FROM `{$this->tableName}` AS `c`
                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
            WHERE  `c`.`is_delete` = 0 and `r`.`content` IS NOT NULL
                ORDER BY `c`.`created` DESC 
                LIMIT {$int_page}, {$int_limit}
	        ";
	        return $this->dbUtil->SelectData( $str_sql, array() );
        }else{
	        $str_sql = "
	            SELECT 
	                `c`.`content`,`c`.`point`,`c`.`created`,`c`.`product_id`,`c`.`user_id`,
	                `r`.`content` as `reply_content`,
	                `r`.`created` as `reply_created`,
	                `u`.`name` as `username`,
	                `u`.`rank` as `userrank`
	            FROM `{$this->tableName}` AS `c`
	                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
	                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
	            WHERE `c`.`product_id` = ?  and `r`.`content` IS NOT NULL  AND `c`.`is_delete` = 0 
	                ORDER BY `c`.`created` DESC
	                LIMIT {$int_page}, {$int_limit}
	        ";
	                //GROUP BY `r`.`comment_id`
	        return $this->dbUtil->SelectData( $str_sql, array( $int_productid ) );
        }
    }
    
    /**
     * use in product page only show top one opinion
     */
    public function getTopOneCommentsByProductId($int_productid, $int_page=null, $int_limit=null){
        if( is_null( $int_page ) ){
            $int_page = 0;
            $int_limit = 20;
        }
        $str_sql = "
            SELECT 
                `c`.`content`,`c`.`point`,`c`.`created`,
                `r`.`content` as `reply_content`,
                `r`.`created` as `reply_created`,
                `u`.`name` as `username`,
                `u`.`rank` as `userrank`
            FROM `{$this->tableName}` AS `c`
                LEFT JOIN `comment_replies` AS `r` ON `c`.`id` = `r`.`comment_id`
                LEFT JOIN `users` AS `u` ON `u`.`id` = `c`.`user_id`
            WHERE `c`.`product_id` = ? AND `c`.`is_delete` = 0 
                
                ORDER BY `c`.`created` DESC 
                LIMIT {$int_page}, {$int_limit}
        ";
                //GROUP BY `r`.`comment_id`
                 //AND `r`.`content`  IS NOT NULL
                
        return $this->dbUtil->SelectData( $str_sql, array( $int_productid ) );
    }
    
/*
* Steve
*/
    public function getCommentInfoByUserId( $int_userid, $int_page = 0, $int_limit = 10 ){
        $param = array($int_userid);
        $sql="
            SELECT SQL_CALC_FOUND_ROWS
                `c`.`id` AS `comment_id`,
                `p`.`id` AS `product_id`,
                `p`.`title` AS `product_title`,
                `c`.`created` AS `comment_created`,
                `c`.`content` AS `comment_content`,
                `c`.`point` AS `comment_point`,
                `cr`.`created` AS `reply_created`,
                `cr`.`content` AS `reply_content`,
                `a`.`username` AS `admin_name`
            FROM `{$this->tableName}` AS `c`
            LEFT JOIN `products` AS `p` ON `c`.`product_id` = `p`.`id`
            LEFT JOIN `comment_replies` AS `cr` ON `c`.`id` = `cr`.`comment_id`
            LEFT JOIN `admins` AS `a` ON `a`.`id` = `cr`.`reply_id`
            WHERE `c`.`user_id` = ? AND `c`.`is_delete` = 0
            ORDER BY `c`.`created` DESC,`cr`.`created` DESC
            LIMIT " . ( $int_page * $int_limit ) . ", {$int_limit}
        ";
        $arr_results = $this->dbUtil->SelectData($sql,$param);
        
        return $arr_results;
    }
    
}

